﻿using Comm;
using Kinlo.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kinlo.Repo
{
    public class AfterWeightFirstRep
    {
        string strConn;
        string strConn1;
        public AfterWeightFirstRep()
        {
            strConn = System.Configuration.ConfigurationManager.ConnectionStrings["WeightDB"].ConnectionString;
            strConn1 = System.Configuration.ConfigurationManager.ConnectionStrings["WeightDB1"].ConnectionString;
        }

        public List<AfterWeightFirst> GetAfterFirst(string time1, string time2, string code, string okngStatus)
        {
            try
            {
                if (string.IsNullOrEmpty(code))
                {
                    code = "%";
                }
                else
                {
                    code = "%" + code + "%";
                }

                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC"? "AfterWeight_Second": "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT * FROM dbo.{4}  WHERE ProductDate BETWEEN '{0}' AND '{1}' AND Code LIKE '{2}' AND Result_One LIKE '{3}' ORDER BY ProductDate DESC ",
                               time1, time2, code, okngStatus, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();

                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex.Message));
                return null;
            }
        }

        public int DeleteAfterWeight(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC"?"AfterWeight_Second": "AfterWeight_Thirdly");
                    var cmd = string.Format(@"DELETE dbo.{1} WHERE Code='{0}'", w.Code, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "DeleteAfterWeight", ex.Message));
                return 0;
            }
        }

        public int Add2(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"INSERT dbo.{0}
                                (ID, ProductDate ,Code ,TrayNum ,TrayIndex ,TrayCol,CodeIndex ,BeforWeight_One ,
                                  AfterWeight_One ,InjectionNum_One ,InjectionNum_Offset_One ,ISSuppled_One ,
                                  SupplementNum_One ,SupplementWeight_One ,Result_One ,RangeOfInjection_One,IsValidate,[Index],BatchNo,BatchNo1,EquNo,EmpNo,JDBatchNo,
                                    InjValueLow,InjValueUp,JudgeType,OnceBeforWeight,Loop,Vacuum,PPressure,NPressure,BeforWeightTime,RetainVal,PTime,NTime,BeforeResult_One,
                                    AfterResult_One)
                        VALUES  (@ID, @ProductDate ,@Code ,@TrayNum ,@TrayIndex ,@TrayCol,@CodeIndex ,@BeforWeight_One ,
                                  @AfterWeight_One ,@InjectionNum_One ,@InjectionNum_Offset_One ,@ISSuppled_One ,
                                  @SupplementNum_One ,@SupplementWeight_One ,@Result_One ,@RangeOfInjection_One,@IsValidate,@Index,@BatchNo,@BatchNo1,@EquNo,@EmpNo,@JDBatchNo ,
                                    @InjValueLow,@InjValueUp,@JudgeType,@OnceBeforWeight,@Loop,@Vacuum,@PPressure,@NPressure,@BeforWeightTime,@RetainVal,@PTime,@NTime,
                                    @BeforeResult_One,@AfterResult_One)", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Code", w.Code == null ? DBNull.Value : (object)w.Code));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayNum", w.TrayNum == null ? DBNull.Value : (object)w.TrayNum));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayIndex", w.TrayIndex == null ? DBNull.Value : (object)w.TrayIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@CodeIndex", w.CodeIndex == null ? DBNull.Value : (object)w.CodeIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayCol", w.TrayCol == null ? DBNull.Value : (object)w.TrayCol));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforWeight_One", w.BeforWeight_One == null ? DBNull.Value : (object)w.BeforWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@AfterWeight_One", w.AfterWeight_One == null ? DBNull.Value : (object)w.AfterWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_One", w.InjectionNum_One == null ? DBNull.Value : (object)w.InjectionNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_Offset_One", w.InjectionNum_Offset_One == null ? DBNull.Value : (object)w.InjectionNum_Offset_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@ISSuppled_One", w.ISSuppled_One == null ? DBNull.Value : (object)w.ISSuppled_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementNum_One", w.SupplementNum_One == null ? DBNull.Value : (object)w.SupplementNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementWeight_One", w.SupplementWeight_One == null ? DBNull.Value : (object)w.SupplementWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@AfterResult_One", w.AfterResult_One == null ? DBNull.Value : (object)w.AfterResult_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforeResult_One", w.BeforeResult_One == null ? DBNull.Value : (object)w.BeforeResult_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@Result_One", w.Result_One == null ? DBNull.Value : (object)w.Result_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@RangeOfInjection_One", w.RangeOfInjection_One == null ? DBNull.Value : (object)w.RangeOfInjection_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@IsValidate", w.IsValidate == null ? DBNull.Value : (object)w.IsValidate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    sqlCmd.Parameters.Add(new SqlParameter("@BatchNo", w.BatchNo == null ? DBNull.Value : (object)w.BatchNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@BatchNo1", w.BatchNo1 == null ? DBNull.Value : (object)w.BatchNo1));
                    sqlCmd.Parameters.Add(new SqlParameter("@EmpNo", w.EmpNo == null ? DBNull.Value : (object)w.EmpNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@EquNo", w.EquNo == null ? DBNull.Value : (object)w.EquNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@JDBatchNo", w.JDBatchNo == null ? DBNull.Value : (object)w.JDBatchNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValueLow", w.InjValueLow == null ? DBNull.Value : (object)w.InjValueLow));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValueUp", w.InjValueUp == null ? DBNull.Value : (object)w.InjValueUp));
                    sqlCmd.Parameters.Add(new SqlParameter("@JudgeType", w.JudgeType == null ? DBNull.Value : (object)w.JudgeType));
                    sqlCmd.Parameters.Add(new SqlParameter("@OnceBeforWeight", w.OnceBeforWeight == null ? DBNull.Value : (object)w.OnceBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@Loop", w.Loop == null ? DBNull.Value : (object)w.Loop));
                    sqlCmd.Parameters.Add(new SqlParameter("@Vacuum", w.Vacuum == null ? DBNull.Value : (object)w.Vacuum));
                    sqlCmd.Parameters.Add(new SqlParameter("@PPressure", w.PPressure == null ? DBNull.Value : (object)w.PPressure));
                    sqlCmd.Parameters.Add(new SqlParameter("@NPressure", w.NPressure == null ? DBNull.Value : (object)w.NPressure));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforWeightTime", w.BeforWeightTime == null ? DBNull.Value : (object)w.BeforWeightTime));
                    sqlCmd.Parameters.Add(new SqlParameter("@RetainVal", w.RetainVal == null ? DBNull.Value : (object)w.RetainVal));
                    sqlCmd.Parameters.Add(new SqlParameter("@PTime", w.PTime == null ? DBNull.Value : (object)w.PTime));
                    sqlCmd.Parameters.Add(new SqlParameter("@NTime", w.NTime == null ? DBNull.Value : (object)w.NTime));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkWeight", w.RWorkWeight == null ? DBNull.Value : (object)w.RWorkWeight));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkInjVal", w.RWorkInjVal == null ? DBNull.Value : (object)w.RWorkInjVal));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkHoldVal", w.RWorkHoldVal == null ? DBNull.Value : (object)w.RWorkHoldVal));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkTime", w.RWorkTime == null ? DBNull.Value : (object)w.RWorkTime));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkOper", w.RWorkOper == null ? DBNull.Value : (object)w.RWorkOper));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkResult", w.RWorkResult == null ? DBNull.Value : (object)w.RWorkResult));
                    //sqlCmd.Parameters.Add(new SqlParameter("@RWorkResultDetail", w.RWorkResultDetail == null ? DBNull.Value : (object)w.RWorkResultDetail)); 
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }

            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "Add2", ex.Message));
                return 0;
            }
        }


        public BeforWeightFirst GetOneInjBeforWeight(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn1))
                {
                    //var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : "AfterWeight_Second";
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.BeforWeight_First WHERE Code='{0}' ORDER BY ProductDate DESC", barcode);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<BeforWeightFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetOneInjBeforWeight", ex.Message));
                return null;
            }
        }
        /// <summary>
        /// 获取存放在前扫码的一注前重
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public CodeFirst GetLocalOneInjBeforWeight(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.Code_Second WHERE Code='{0}' ORDER BY ProductDate DESC", barcode);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<CodeFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetOneInjBeforWeight", ex.Message));
                return null;
            }
        }

        public int AddEczyResult(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var cmd = string.Format("InsertWeightToMes");
                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.CommandType = CommandType.StoredProcedure;
                    var code = (new SqlParameter("@Code", barcode));

                    var ReturnCount = new SqlParameter
                    {
                        ParameterName = "ReturnCount",
                        Size = 50,
                        SqlDbType = SqlDbType.Int,
                        Direction = ParameterDirection.Output
                    };
                    var ReturnCode = new SqlParameter
                    {
                        ParameterName = "ReturnCode",
                        Size = 50,
                        SqlDbType = SqlDbType.Int,
                        Direction = ParameterDirection.Output
                    };
                    var ReturnMsg = new SqlParameter
                    {
                        ParameterName = "ReturnMsg",
                        Size = 500,
                        SqlDbType = SqlDbType.NVarChar,
                        Direction = ParameterDirection.Output
                    };

                    sqlCmd.Parameters.Add(code);
                    sqlCmd.Parameters.Add(ReturnCount);
                    sqlCmd.Parameters.Add(ReturnCode);
                    sqlCmd.Parameters.Add(ReturnMsg);
                    conn.Open();
                    var reader = sqlCmd.ExecuteNonQuery();
                    var count = Convert.ToInt32(ReturnCount.Value.ToString());
                    var retrunCode = Convert.ToInt32(ReturnCode.Value.ToString());
                    if (retrunCode > 0)
                    {
                        Logger.Error(string.Format("{0} 插入MES 中间表异常 {1}", ReturnCode.Value, ReturnMsg.Value));
                    }
                    return count;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "EczyResultRep", "AddEczyResult", ex.Message));
                return 0;
            }
        }


        public IList<AfterWeightFirst> GetAfterFirst1()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT TOP 1000 * FROM dbo.{0} WHERE IsValidate=1 AND	ProductDate>DATEADD(DAY,-7,GETDATE()) ORDER BY ProductDate DESC", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var AfterWeightFirsts = sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>();
                    if (AfterWeightFirsts.Count > 0)
                        return AfterWeightFirsts;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst1", ex.Message));
                return null;
            }
        }

        public DataTable GetDataBase()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT REPLACE(CONVERT(NVARCHAR(19),T.ProductDate,121),'-','/') AS ProductDate  ,
                   T.[1] ,T.[2] ,T.[3] ,T.[4] ,T.[5] ,T.[6] ,T.[7] ,T.[8] ,T.[9] , T.[10] , T.[11] , T.[12]
                    from dbo.{0} as C 
                    PIVOT(MIN(InjectionNum_One) FOR [TrayCol] IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS T", table);
                    SqlDataAdapter da = new SqlDataAdapter(cmd, conn);
                    DataTable dt = new DataTable();
                    da.Fill(dt);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetDataBase", ex.Message));
                return null;
            }
        }

        public AfterWeightFirst GetAfterWeightByBarcode(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn1))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.{1} WHERE Code='{0}' ORDER BY ProductDate DESC", barcode, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterWeightByBarcode", ex), false);
                return null;
            }

        }

        /// <summary>
        /// 查一注后称重数据
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public AfterWeightFirst GetAfterWeight_First(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn1))
                {
                    var table = "AfterWeight_First";
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.{1} WHERE Code='{0}' ORDER BY ProductDate DESC", barcode, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterWeightByBarcode", ex), false);
                return null;
            }

        }

        /// <summary>
        /// 查二注后称重数据
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public AfterWeightFirst GetAfterWeight_Second(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn1))
                {
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.AfterWeight_Second WHERE Code='{0}' ORDER BY ProductDate DESC", barcode);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetOnceAfterWeightByBarcode", ex), false);
                return null;
            }

        }

        /// <summary>
        /// 查一注后称重数据
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public AfterWeightFirst GetOnceAfterWeightByBarcode(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn1))
                {
                    var cmd = string.Format(@"SELECT TOP 1 * FROM dbo.AfterWeight_First WHERE Code='{0}' ORDER BY ProductDate DESC", barcode);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteReader().ReaderToList<AfterWeightFirst>().FirstOrDefault();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetOnceAfterWeightByBarcode", ex), false);
                return null;
            }
        }



        public int UpdateAfterWeightFirst(AfterWeightFirst w)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"UPDATE  dbo.{0} set 
                        IsRead=@IsRead ,ProductDate=@ProductDate ,Code=@Code ,CodeIndex=@CodeIndex ,TrayNum =@TrayNum,TrayIndex=@TrayIndex ,
                        TrayCol=@TrayCol ,BeforWeight_One=@BeforWeight_One ,AfterWeight_One=@AfterWeight_One,InjectionNum_One=@InjectionNum_One ,
                        InjectionNum_Offset_One =@InjectionNum_Offset_One,ISSuppled_One=@ISSuppled_One ,SupplementNum_One=@SupplementNum_One ,
                        SupplementWeight_One=@SupplementWeight_One,Result_One=@Result_One ,RangeOfInjection_One =@RangeOfInjection_One,
                        IsValidate =@IsValidate,[Index]=@index ,BatchNo =@BatchNo,BatchNo1 =@BatchNo1, EquNo=@EquNo ,EmpNo=@EmpNo ,JDBatchNo=@JDBatchNo ,InjValueLow =@InjValueLow,
                        InjValueUp=@InjValueUp ,JudgeType=@JudgeType,OnceBeforWeight=@OnceBeforWeight,Loop=@Loop,Vacuum=@Vacuum,PPressure=@PPressure,
                        NPressure=@NPressure,BeforWeightTime=@BeforWeightTime,RetainVal=@RetainVal,PTime=@PTime,NTime=@NTime,BeforeResult_One=@BeforeResult_One,
                        AfterResult_One=@AfterResult_One WHERE ID=@ID", table);

                    var sqlCmd = new SqlCommand(cmd, conn);
                    sqlCmd.Parameters.Add(new SqlParameter("@ID", w.ID == null ? DBNull.Value : (object)w.ID));
                    sqlCmd.Parameters.Add(new SqlParameter("@IsRead", w.IsRead == null ? DBNull.Value : (object)w.IsRead));
                    sqlCmd.Parameters.Add(new SqlParameter("@ProductDate", w.ProductDate == null ? DBNull.Value : (object)w.ProductDate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Code", w.Code == null ? DBNull.Value : (object)w.Code));
                    sqlCmd.Parameters.Add(new SqlParameter("@CodeIndex", w.CodeIndex == null ? DBNull.Value : (object)w.CodeIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayNum", w.TrayNum == null ? DBNull.Value : (object)w.TrayNum));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayIndex", w.TrayIndex == null ? DBNull.Value : (object)w.TrayIndex));
                    sqlCmd.Parameters.Add(new SqlParameter("@TrayCol", w.TrayCol == null ? DBNull.Value : (object)w.TrayCol));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforWeight_One", w.BeforWeight_One == null ? DBNull.Value : (object)w.BeforWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@AfterWeight_One", w.AfterWeight_One == null ? DBNull.Value : (object)w.AfterWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_One", w.InjectionNum_One == null ? DBNull.Value : (object)w.InjectionNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjectionNum_Offset_One", w.InjectionNum_Offset_One == null ? DBNull.Value : (object)w.InjectionNum_Offset_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@ISSuppled_One", w.ISSuppled_One == null ? DBNull.Value : (object)w.ISSuppled_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementNum_One", w.SupplementNum_One == null ? DBNull.Value : (object)w.SupplementNum_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@SupplementWeight_One", w.SupplementWeight_One == null ? DBNull.Value : (object)w.SupplementWeight_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@AfterResult_One", w.AfterResult_One == null ? DBNull.Value : (object)w.AfterResult_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforeResult_One", w.BeforeResult_One == null ? DBNull.Value : (object)w.BeforeResult_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@Result_One", w.Result_One == null ? DBNull.Value : (object)w.Result_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@RangeOfInjection_One", w.RangeOfInjection_One == null ? DBNull.Value : (object)w.RangeOfInjection_One));
                    sqlCmd.Parameters.Add(new SqlParameter("@IsValidate", w.IsValidate == null ? DBNull.Value : (object)w.IsValidate));
                    sqlCmd.Parameters.Add(new SqlParameter("@Index", w.Index == null ? DBNull.Value : (object)w.Index));
                    sqlCmd.Parameters.Add(new SqlParameter("@BatchNo", w.BatchNo == null ? DBNull.Value : (object)w.BatchNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@BatchNo1", w.BatchNo1 == null ? DBNull.Value : (object)w.BatchNo1));
                    sqlCmd.Parameters.Add(new SqlParameter("@EmpNo", w.EmpNo == null ? DBNull.Value : (object)w.EmpNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@EquNo", w.EquNo == null ? DBNull.Value : (object)w.EquNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@JDBatchNo", w.JDBatchNo == null ? DBNull.Value : (object)w.JDBatchNo));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValueLow", w.InjValueLow == null ? DBNull.Value : (object)w.InjValueLow));
                    sqlCmd.Parameters.Add(new SqlParameter("@InjValueUp", w.InjValueUp == null ? DBNull.Value : (object)w.InjValueUp));
                    sqlCmd.Parameters.Add(new SqlParameter("@JudgeType", w.JudgeType == null ? DBNull.Value : (object)w.JudgeType));
                    sqlCmd.Parameters.Add(new SqlParameter("@OnceBeforWeight", w.OnceBeforWeight == null ? DBNull.Value : (object)w.OnceBeforWeight));
                    sqlCmd.Parameters.Add(new SqlParameter("@Loop", w.Loop == null ? DBNull.Value : (object)w.Loop));
                    sqlCmd.Parameters.Add(new SqlParameter("@Vacuum", w.Vacuum == null ? DBNull.Value : (object)w.Vacuum));
                    sqlCmd.Parameters.Add(new SqlParameter("@PPressure", w.PPressure == null ? DBNull.Value : (object)w.PPressure));
                    sqlCmd.Parameters.Add(new SqlParameter("@NPressure", w.NPressure == null ? DBNull.Value : (object)w.NPressure));
                    sqlCmd.Parameters.Add(new SqlParameter("@BeforWeightTime", w.BeforWeightTime == null ? DBNull.Value : (object)w.BeforWeightTime));
                    sqlCmd.Parameters.Add(new SqlParameter("@RetainVal", w.RetainVal == null ? DBNull.Value : (object)w.RetainVal));
                    sqlCmd.Parameters.Add(new SqlParameter("@PTime", w.PTime == null ? DBNull.Value : (object)w.PTime));
                    sqlCmd.Parameters.Add(new SqlParameter("@NTime", w.NTime == null ? DBNull.Value : (object)w.NTime));
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateAfterWeightFirst", ex), false);
                return 0;
            }
        }

        public int UpdateExportDataStatus(DateTime date1, DateTime date2)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@" UPDATE dbo.{2} SET IsExport=1 WHERE (IsExport=0 or IsExport is NULL) AND ProductDate BETWEEN '{0}' and '{1}'",
                           date1.ToString("yyyy-MM-dd HH:mm:ss"), date2.ToString("yyyy-MM-dd HH:mm:ss"), table);
                    //var cmd = string.Format(@"UPDATE  AfterWeight_First SET IsExport=1 WHERE ID='{0}'  ", id);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex.Message));
                return 0;
            }
        }

        public List<AfterWeightFirst> GetAfterFirstByTime(DateTime date1, DateTime date2)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@" SELECT * FROM {2} WHERE (IsExport=0 or IsExport is NULL) AND ProductDate BETWEEN '{0}' and '{1}'",
                        date1.ToString("yyyy-MM-dd HH:mm:ss"), date2.ToString("yyyy-MM-dd HH:mm:ss"), table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirstByTime", ex.Message));
                return null;
            }
        }

        public List<UnExportDate> GetUnExportDates()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT DISTINCT(CONVERT(NVARCHAR(10),ProductDate,121)) Date FROM dbo.{0} WHERE (IsExport=0 OR IsExport IS NULL)
                    AND ( ProductDate< CONVERT(NVARCHAR(10),GETDATE(),121)) ORDER BY Date  ", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var unExports = sqlCmd.ExecuteReader().ReaderToList<UnExportDate>();
                    if (unExports.Count > 0)
                        return unExports;
                }
                return null;
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetUnExportDates", ex.Message));
                return null;
            }
        }
        public List<AfterWeightFirst> GetAfterFirst(string date)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT * FROM dbo.{1} WHERE  CONVERT(NVARCHAR(10),ProductDate,121)='{0}'", date, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst", ex.Message));
                return null;
            }
        }
        public List<AfterWeightFirst2> GetAfterFirst2(DateTime time)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT * FROM dbo.{1} WHERE IsExport <> 1 or IsExport is NULL AND ProductDate<='{0}'", time, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst2>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetAfterFirst2", ex.Message));
                return null;
            }
        }

        public int UpdateExportDataStatus(string date)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"UPDATE dbo.{1} SET IsExport=1 WHERE  CONVERT(NVARCHAR(10),ProductDate,121)='{0}'", date, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus", ex.Message));
                return 0;
            }
        }
        public int UpdateExportDataStatus2(DateTime date)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"UPDATE dbo.{1} SET IsExport=1 WHERE  ProductDate<='{0}'", date, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateExportDataStatus2", ex.Message));
                return 0;
            }
        }

        public int UpdateUplodWeight2Status(string barcode)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"UPDATE dbo.{1} SET IsUpload=1,UploadDate=GETDATE() WHERE Code='{0}'", barcode, table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateUplodWeight2Status", ex.Message));
                return 0;
            }
        }

        public List<AfterWeightFirst> GetUnloadWeight2()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "AfterWeight_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "AfterWeight_Second" : "AfterWeight_Thirdly");
                    var cmd = string.Format(@"SELECT * FROM dbo.{0} WHERE (IsUpload!=1 OR IsUpload IS NULL)  and RetranCount<5 ORDER BY ProductDate ", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<AfterWeightFirst>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetUnloadWeight2", ex.Message));
                return null;
            }
        }

        public int AddToMesRetran(RetranToMesInfo r)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {

                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "UploadToMesFailRetran_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "UploadToMesFailRetran_Second" : "UploadToMesFailRetran_Thirdly");
                    var cmd = string.Format(@"INSERT dbo.{0}( ID ,Barcode,Message ,Date ,IsUpload ,UploadDate,RetranCount)VALUES  ('{1}','{2}','{3}','{4}','{5}','{6}','{7}' )",
                                            table, r.ID, r.Barcode, r.Message, r.Date, r.IsUpload, r.UploadDate, r.RetranCount);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "AddToMesRetran", ex.Message));
                return 0;
            }
        }

        public List<RetranToMesInfo> GetMesTranInfo()
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {
                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "UploadToMesFailRetran_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "UploadToMesFailRetran_Second" : "UploadToMesFailRetran_Thirdly");
                    var cmd = string.Format(@"SELECT * FROM dbo.{0} WHERE IsUpload<>1 and RetranCount<5 ORDER BY Date  ", table);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    var reader = sqlCmd.ExecuteReader();
                    return reader.ReaderToList<RetranToMesInfo>();
                }
            }
            catch (Exception ex)
            {
                Logger.Log(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "GetMesTranInfo", ex.Message));
                return null;
            }
        }

        public int UpdateMesRetranStatus(Guid? id, bool result, int count = 1)
        {
            try
            {
                using (var conn = new SqlConnection(strConn))
                {

                    var table = GlobalValues.OpType.ToUpper() == "YC" ? "UploadToMesFailRetran_First" : (GlobalValues.OpType.ToUpper() == "EC" ? "UploadToMesFailRetran_Second" : "UploadToMesFailRetran_Thirdly");
                    var cmd = string.Format(@"UPDATE dbo.{0} SET IsUpload='{3}',RetranCount='{2}',UploadDate=GETDATE() WHERE ID='{1}'", table, id, count, result);
                    var sqlCmd = new SqlCommand(cmd, conn);
                    conn.Open();
                    return sqlCmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Logger.Error(string.Format("{0} {1} {2}", "AfterWeightFirstRep", "UpdateMesRetranStatus", ex.Message));
                return 0;
            }
        }

    }
}
